# -*- coding: utf-8 -*-
# author: zhaofeng-shu33
from docx import Document
from docx.shared import Pt
import docx
from docx.enum.text import WD_ALIGN_PARAGRAPH
from copy import deepcopy
import MySQLdb # belongs to mysqlclient
import os
import pdb
import re
import time
from program.utility import get_docx, get_basic_info
from program.student_type import dir_names
# initialization of database related variable
db = MySQLdb.connect(host ="localhost",user="root", db="student", passwd=os.getenv("PASSWORD"), charset="utf8")
cursor = db.cursor()
gender_dic = {'Female':'女','Male':'男'}
base_dir_name = 'output'
required_field_list = ['student_name', 'student_gender', 'student_id', 'student_mobilephone', 'student_major', 
'student_class', 'student_group_leader', 'student_mentor_name', 'student_relative_name', 'student_relative_phone',
'student_house_address', 'student_dormitory']
cell_map = [(0,2), (0,4), (1,2), (1,4), (2,2), (2,2), (2,4), (3,2), (4,2), (4,4), (5,2)]
required_field_str = ','.join(required_field_list)
removed_num = 0
added_num = 0

                


def add_formated_str(para, added_str):
    run = para.add_run(added_str)
    run.font.bold = True
    if(re.match('[0-9]+',added_str)): # suppose all are numbers
        run.font.name = 'Calibri'
    else: # suppose all are Chinese characters
        run.font.name = 'SimSun'
    run.font.size = Pt(14)

def add_info(doc, dic, floor_cnt):
    if(dic.get(floor_cnt)):
        for index,i in enumerate(dic[floor_cnt]):
            t = doc.tables[-(3-index)] # get the last table
            t.cell(0,2).text = i[0] # name
            t.cell(0,4).text = gender_dic[i[1]] # gender
            t.cell(1,2).text = i[2] # student_id

def student_select(cursor, house_number_str, house_id):
    '''
    select student from given house

    Parameters
    ----------
    house_number_str : [string] from ['1','2','4','5']
    house_id [string] contains floor and number information

    Returns
    -------
    out : [list] each item = [required_field_list_content, mobile_of_mentor]
    '''
    global required_field_str
    cursor.execute('select {0} from qinghua where student_dormitory like \'荷{1}-{2}\' order by student_id desc;'.format(required_field_str, house_number_str, house_id))
    # 0-3 results are returned
    results = cursor.fetchall()
    if len(results) > 3:
        results = results[:3]
    if results is None:
        print("Empty for 荷{0}-%{1}".format(house_number_str, house_id))
        return None
    extended_results = []
    for item in results: # tuple is returned
        extended_results.append([item,None])
        mentor_name = item[7]
        if(mentor_name is not None): # mentor exists
            cursor.execute('select teacher_mobile from qinghua_teacher where teacher_name = \'%s\''%mentor_name)
            mentor_mobile = cursor.fetchone()
            if(mentor_mobile is not None):
                extended_results[-1][1] = mentor_mobile[0]
    return extended_results

def update_table(table, info_item):
    '''
    update empty content in  `table` from info_item

    Parameters
    ----------
    table: docx.table.Table object empty contents in table
    info_item list [required_field_list_content, mobile_of_mentor]

    Returns
    -------
    out: None
    '''    
    global cell_map
    for index, tuple_index in enumerate(cell_map):
        i,j = tuple_index
        if(table.cell(i,j).text == '' and info_item[0][index] is not None):
            table.cell(i,j).text = info_item[0][index]
    if(table.cell(3,4).text == '' and info_item[1] is not None): # mentor_mobile
        table.cell(3,4).text = info_item[1]

def insert_table(table, info_item):
    '''
    insert contents into `table` from info_item

    Parameters
    ----------
    table: docx.table.Table object empty contents in table
    info_item list [required_field_list_content, mobile_of_mentor]

    Returns
    -------
    out: None
    '''
    name = info_item[0][0]    
    table.cell(0,2).text = name # name
    try:
        table.cell(0,4).text = gender_dic[info_item[0][1]]
    except KeyError as e:
        pdb.set_trace()
    table.cell(1,2).text = info_item[0][2].lstrip('1')
    mobile = info_item[0][3]
    if(mobile):
        table.cell(1,4).text = mobile
    major = info_item[0][4]
    if(major):
        table.cell(2,2).text = major
    group_leader = info_item[0][6]
    if(group_leader):
        if(info_item[1] is not None):
            group_leader_phone = '\n' + info_item[1]
        else:
            group_leader_phone = ''
        table.cell(2,4).text = group_leader + group_leader_phone # group leader phone
    mentor_name = info_item[0][7]
    if(mentor_name):
        table.cell(3,2).text = mentor_name
    mentor_phone = info_item[1]
    if(mentor_phone):
        table.cell(3,4).text = mentor_phone
    relative_name = info_item[0][8]
    if(relative_name):
        if(re.match('[0-9]+',relative_name)):
            print('relative_name cannot contain numbers')
            pdb.set_trace()
        table.cell(4,2).text = relative_name
    relative_phone = info_item[0][9]
    if(relative_phone):
        table.cell(4,4).text = relative_phone
    house_address = info_item[0][10]
    if(house_address):
        table.cell(5,2).text = house_address
    global added_num
    added_num += 1

def iter_rId_parts(d):
    """
    Generate exactly one reference to each of the parts in the package by
    performing a depth-first traversal of the rels graph.
    """
    def walk_parts(source, visited=list()):
        for rId, rel in source.rels.items():
            if rel.is_external:
                continue
            part = rel.target_part
            if part in visited:
                continue
            visited.append(part)
            yield (rId, part)
            new_source = part
            for rId, part in walk_parts(new_source, visited):
                yield (rId, part)

    for item in walk_parts(d.part.package):
        yield item    

def remove_images(rid, doc):    
    for key, part in iter_rId_parts(doc):
        if(key == rid):
            if(type(part) is not docx.parts.image.ImagePart):
                print('error not imagepart')
            part._blob = b''
            return
    #return_value = walk_rId_parts_remove_images(rid, d.part.package)
    pdb.set_trace()

def remove_leaved(table, student_name, student_id, student_dormitory, doc):
    HAS_DORMITORY = False
    if HAS_DORMITORY == True:
        return False
    para = table.cell(0,0).paragraphs[0]
    Ptr = para._p.find('.//v:imagedata',namespaces=para._p.nsmap)
    if(Ptr is not None):
        rid = Ptr.get('{%s}id'%Ptr.nsmap['r'])                                
        remove_images(rid, doc)
    else: # existing person does not have image
        pass
    para = para.clear()
    table.cell(5,2)._element.text = ''
    for i in range(0,5):
        for j in range(1,3):
            table.cell(i,2*j).text = ''
    global removed_num    
    removed_num += 1
    return True

def get_available_table(tables):
    '''
    get the first empty table in tables
    '''
    for table in tables:
        if(table.cell(0,2).text==''):   
            return table 
    return None

def update_tables(tables, info_items, doc): #info_items = [(infos),mentor_mobile]
    '''
    update three tables, which correspond to one house with `info_items`

    Parameters
    ----------
    tables : list  [table_1, table_2, table_3], each item of type `docx.table.Table`
    info_items: list each item = [required_field_list_content, mobile_of_mentor]
    docx.document.Document object

    Returns
    -------
    out : None
    '''
    if(len(info_items) > 3):
        print("A dormitory cannot have more than three persons, at %s"%info_items[0][0][-1])
        return
    if(len(info_items) == 0):
        return
    table_name_list = []
    table_list = [] #correspond to table_name_list
    new_students_name = [i[0][0] for i in info_items]
    for table in tables:
        table_student_name = table.cell(0,2).text.strip()
        if(table_student_name == ''):
            continue
        student_id_value =  table.cell(1,2).text
        try:
            student_dormitory = info_items[0][0][-1]
        except Exception as e:
            pdb.set_trace()
        if new_students_name.count(table_student_name) == 0:
            is_removed = remove_leaved(table, table_student_name, student_id_value, student_dormitory, doc)
        else:
            is_removed = False
        if(table_student_name and is_removed == False):
            table_name_list.append(table_student_name)
            table_list.append(table)
    for info_item in info_items:
        student_name = info_item[0][0]
        if(table_name_list.count(student_name) > 0):
            update_table(table_list[table_name_list.index(student_name)], info_item)
            continue
        table = get_available_table(tables)
        if(table is None):
            pdb.set_trace()
        try:
            insert_table(table, info_item)
        except Exception as e:
            pdb.set_trace()
    return

# do the work
def update_one(dir_name, file_name, using_base = True):
    '''
    update one docx file content (save it)

    Parameters
    ----------
    dir_name : string 
    file_name : string

    Returns
    -------
    out: None
    '''
    global base_dir_name
    if(using_base):
        f = open(os.path.join(base_dir_name, dir_name, file_name), 'rb')
    else:
        f = open(os.path.join(dir_name, file_name), 'rb')
    d = Document(f)
    house_number_str, start_floor_number, end_floor_number, floor_index_str = get_basic_info(dir_name, file_name)
    try:
        assert(len(d.tables)==3*(end_floor_number - start_floor_number + 1))
    except AssertionError as e:
        pdb.set_trace()
    table_index = 0
    for magic in range(start_floor_number, end_floor_number+1):
        house_id = str(magic) + floor_index_str
        student_item = student_select(cursor, house_number_str, house_id) # extra 25-1-1
        if(student_item is None): # no students in this house
            table_index += 3
            continue
        update_tables(d.tables[table_index:table_index+3], student_item, d)
        table_index += 3
        # handle empty table
    write_file_name = os.path.join(base_dir_name, dir_name, file_name)
    d.save(write_file_name)

if __name__ == '__main__':
    start_time = time.time()
    for dir_name in dir_names:
        for file_name in get_docx(dir_name):
            update_one(dir_name, file_name, using_base=True)
    end_time = time.time()
    print("Statistics: ")
    print("%d items removed"%removed_num)
    print("%d items added"%added_num)
    print("total time used: %0.2f"%(end_time - start_time))